# @Time    : 2019/12/31 15:50
# @File    : Excel多个Sheet合并.py
# @Date    : 2019/12/31
# @Author  : Yuwenjun
# @Desc    : 合并Excel多个sheet
import time

import pandas as pd
import os
import pymysql
from pymongo import MongoClient
import requests

# db = pymysql.connect(host='192.168.0.37', user='will_art', passwd='123456', db='will_art')
# client = MongoClient("192.168.0.37", 27017)
# mongodb = client.tasks
# mongodb.authenticate('tasks', '123456')

# db = pymysql.connect(host='39.97.250.105', user='will_art', passwd='123456', db='will_art')
client = MongoClient("39.97.250.105", 27018)
mongodb = client.tasks
mongodb.authenticate('tasks', '123456')


class MergeExcel:
    def __init__(self, header, col, sample, is_filter_number, input_path):
        self.input_path = input_path
        self.header = header
        self.col = col
        self.sample = sample
        self.is_filter_number = is_filter_number
        self.col_tag = False

    def read_excel(self, path):
        df = None
        xls_data = pd.ExcelFile(path)
        for sheet_name in xls_data.sheet_names:
            _df = xls_data.parse(sheet_name=sheet_name, header=self.header, encoding='utf-8')
            # _df = _df.loc[~_df[0].str.contains("[0-9]")] if self.is_filter_number else _df

            df = pd.concat([df, _df], ignore_index=True)
            if sheet_name == "Sheet2":
                print("泰米尔不合格")
                # 第一列的长度
                lenth = len(_df[0])
                task_name_list = []
                checkerName_list = []
                checkerName_cn_list = []
                n = 1
                while n < lenth:
                    # 任务名称
                    task_name = _df[3][n]
                    taskInfo = get_task_info_by_name(task_name)
                    print(taskInfo)
                    checkers = taskInfo[0]['checkers']
                    for checker in checkers:
                        checkerName_list.append(checker['name'])
                        checkerName_cn_list.append(checker['displayName'])
                        task_name_list.append(task_name)

                    n += 1
                
                #写
                dic1 = {'checkerName_list': checkerName_list
                        ,'checkerName_cn_list': checkerName_cn_list
                        ,'task_name_list': task_name_list
                }
                df = pd.DataFrame(dic1)
                df.to_excel("C:\\Users\\Admin\\Desktop\\test\\"+'2020-0511.xlsx', index=False)

        if self.col is None:
            return df.drop_duplicates()
        elif not self.col_tag:
            return df[[int(index) for index in self.col.split(",")]].dropna().drop_duplicates()
        else:
            return df[[index for index in self.col.split(",")]].dropna().drop_duplicates()

    # 反转字符串
    def reversal(self, text_name):
        reversal_string = list(text_name)
        return "".join(reversal_string[::-1])
        # 处理字符

    def deal_string(self, text_name):
        # 反转字符
        text_name = self.reversal(text_name)
        text_names = text_name.split("_", 1)
        text_names_one = text_names[0].split(".", 1)
        text_name_new = text_names_one[0] + "." + text_names[1]
        return self.reversal(text_name_new)

    def run(self):
        if os.path.isdir(self.input_path):
            excel_names = [name for name in os.listdir(self.input_path) if name.endswith((".xls", ".xlsx"))]
        
        _df = self.read_excel(self.input_path)

def get_user_info(self,user_id):
    cusor = self.db.cursor()
    #sql = 'select * from user where id = "{user_id}"'.format(user_id)
    sql = 'select * from user where id = ' + user_id
    cusor.execute(sql)
    row = cusor.fetchone()
    if row:
        user_info = row[0]
    else:
        user_info = 0
    return user_info

def get_task_info_by_name(task_name):
    # 数据集
    tasks = mongodb['tasks']
    # taskinfos = list(tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}}))
    taskinfos = list(tasks.find({'taskName': task_name}))
    # taskinfos = tasks.find({'taskName':  task_name })
    # taskinfos = tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}})
    return taskinfos


def get_text_info_by_colNameAndaudio_anme(colName, audio_name):
    # 数据集
    dataset = mongodb[colName]
    # taskinfos = list(tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}}))
    # col_info = list(dataset.find({'url':  re.compile(audio_name)}))
    col_info = list(dataset.find({'url': {"$regex": "300.wav"}}))
    # taskinfos = tasks.find({'taskName':  task_name })
    # taskinfos = tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}})
    return col_info


if __name__ == '__main__':
    header = ""
    col = ""
    is_filter_number = ""
    sample = ""
    # header = input("请确认Excel是否包含表头，0无表头，其它有表头，默认为0：")
    # col = input("请指定Excel各sheet需要合并的列，0代表A列，依此类推，默认合并全部：")
    # is_filter_number = input("请确认是否过滤数字，输入1过滤，默认不过滤：")
    # sample = input("是否需要抽样合并，若需要输入每份Excel随机抽取的行数（大于1的整数）或者百分比（小数），\n默认不抽样：")

    # input_path = input("请输入Excel文件夹路径或者单个文件地址：")
    input_path = "C:\\Users\\Admin\\Desktop\\test\\after-泰卢固不合格音频(4)(1).xlsx"
    if not input_path:
        print("请确认Excel的文件夹存放路径是否输入完整")
        exit(1)
    config = {
        "header": None if not header else header,
        "col": None if not col else col,
        "sample": None if not sample else sample,
        "is_filter_number": True if is_filter_number == "1" else False,
        "input_path": input_path
    }

    main = MergeExcel(**config)
    main.run()

    # C:\Users\Admin\Desktop\test\泰米尔不合格音频名称.xlsx
    # C:\Users\Admin\Desktop\test\不合格数据整理.xlsx
    # C:\Users\Admin\Desktop\test\after-泰卢固不合格音频(4)(1).xlsx
